-- =============================================
-- This sp creates a new content record based on the content record passed in
--
-- Modifications
-- 09/08/2003    E.Tatsui    Created
-- =============================================

CREATE        PROCEDURE [dbo].[amsp_CMCopyContentRow]
  @InContentID numeric,
  @InContactID numeric,
  @OutContentID numeric OUTPUT
AS
BEGIN
  DECLARE
    @ColumnList nvarchar(4000),
    @InsertQuery nvarchar(4000)
  
  CREATE TABLE #SkipColumn (SkipColumnName varchar(255))
  
  -- Copy the content row
  EXECUTE amsp_GetTableColumnList 'Content', @ColumnList OUTPUT, 'N', 'WorkflowStatusCode', 'PreviousContentID', 'PublishDateTime', 'ContactID'
  SET @InsertQuery = 'INSERT INTO Content (' + @ColumnList + ',WorkflowStatusCode, PreviousContentID, ContactID) SELECT ' + @ColumnList + ',''W'',' + CAST(@InContentID AS varchar) + ',' + CAST(@InContactID AS varchar)+  ' FROM Content WHERE ContentID = ' + CAST(@InContentID AS varchar)
  EXECUTE sp_executesql @InsertQuery
  SET @OutContentID = @@Identity
  
  -- Copy any Content_HTML rows
  EXECUTE amsp_GetTableColumnList 'Content_HTML', @ColumnList OUTPUT
  SET @InsertQuery = 'INSERT INTO Content_HTML (' + @ColumnList + ',ContentID) SELECT ' + @ColumnList + ',' + CAST(@OutContentID AS varchar) + ' FROM Content_HTML WHERE ContentID = ' + CAST(@InContentID AS varchar)
  EXECUTE sp_executesql @InsertQuery

  -- Copy any Content_Link rows
  EXECUTE amsp_GetTableColumnList 'Content_Link', @ColumnList OUTPUT, 'N', 'ContentID', 'PreviousContentLinkID'
  SET @InsertQuery = 'INSERT INTO Content_Link (' + @ColumnList + ',ContentID, PreviousContentLinkID) SELECT ' + @ColumnList + ',' + CAST(@OutContentID AS varchar) + ',CAST(ContentLinkID AS varchar)' + ' FROM Content_Link WHERE ContentID = ' + CAST(@InContentID AS varchar)
  EXECUTE sp_executesql @InsertQuery

  -- OK, Now if any of the Content_Link rows were specifying the old Content ID, let's fix them.
  UPDATE Content_Link
     SET LinkURL = Replace(LinkURL, 'ContentID=' + Convert(varchar(10), @InContentID), 'ContentID=' + Convert(varchar(10), @OutContentID))
   WHERE ContentID = @OutContentID

  -- Copy any Content_File rows
  EXECUTE amsp_GetTableColumnList 'Content_File', @ColumnList OUTPUT, 'N', 'ContentID','PreviousContentFileID'
  SET @InsertQuery = 'INSERT INTO Content_File (' + @ColumnList + ',ContentID,PreviousContentFileID) SELECT ' + @ColumnList + ',' + CAST(@OutContentID AS varchar) + ',CAST(ContentFileID AS varchar) FROM Content_File WHERE ContentID = ' + CAST(@InContentID AS varchar)
  EXECUTE sp_executesql @InsertQuery

  -- Copy any Tagged_Page_Interest_Category rows
  EXECUTE amsp_GetTableColumnList 'Tagged_Page_Interest_Category', @ColumnList OUTPUT, 'Y', 'ContentID'
  SET @InsertQuery = 'INSERT INTO Tagged_Page_Interest_Category (' + @ColumnList + ',ContentID) SELECT ' + @ColumnList + ',' + CAST(@OutContentID AS varchar) + ' FROM Tagged_Page_Interest_Category WHERE ContentID = ' + CAST(@InContentID AS varchar)
  EXECUTE sp_executesql @InsertQuery
  
  -- Copy any Component_Interest_Category rows
  EXECUTE amsp_GetTableColumnList 'Component_Interest_Category', @ColumnList OUTPUT,'Y','ComponentID'
  SET @InsertQuery = 'INSERT INTO Component_Interest_Category (' + @ColumnList + ',ComponentID) SELECT ' + @ColumnList + ',' + CAST(@OutContentID AS varchar) + ' FROM Component_Interest_Category WHERE ComponentCode = ''CM'' AND ComponentID = ' + CAST(@InContentID AS varchar)
  EXECUTE sp_executesql @InsertQuery

  -- Copy any Content_Security_Group rows
  EXECUTE amsp_GetTableColumnList 'Content_Security_Group', @ColumnList OUTPUT,'Y','ContentID'
  SET @InsertQuery = 'INSERT INTO Content_Security_Group (' + @ColumnList + ',ContentID) SELECT ' + @ColumnList + ',' + CAST(@OutContentID AS varchar) + ' FROM Content_Security_Group WHERE ContentID = ' + CAST(@InContentID AS varchar)
  EXECUTE sp_executesql @InsertQuery

  EXECUTE amsp_GetTableColumnList 'Content_Change_Request', @ColumnList OUTPUT,'N','ContentID'
  SET @InsertQuery = 'INSERT INTO Content_Change_Request (' + @ColumnList + ',ContentID) SELECT ' + @ColumnList + ',' + CAST(@OutContentID AS varchar) + ' FROM Content_Change_Request WHERE RequestStatusCode = ''A'' AND ContentID = ' + CAST(@InContentID AS varchar)
  EXECUTE sp_executesql @InsertQuery

  -- Copy any Content_Publish_Server rows (Not used for this version of CM)
  --EXECUTE amsp_GetTableColumnList 'Content_Publish_Server', @ColumnList OUTPUT,'Y','ContentID'
  --SET @InsertQuery = 'INSERT INTO Content_Publish_Server (' + @ColumnList + ',ContentID) SELECT ' + @ColumnList + ',' + CAST(@OutContentID AS varchar) + ' FROM Content_Publish_Server WHERE ContentID = ' + CAST(@InContentID AS varchar)
  --EXECUTE sp_executesql @InsertQuery
  -- Create the Content_Workflow_Log entry
  INSERT INTO Content_Workflow_Log (ContentID, WorkflowStatusCode, ContactID, ChangeDateTime)
  VALUES (@OutContentID,'W',@InContactID, CURRENT_TIMESTAMP)
END

GO